package org.lanqiao.tjut.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lanqiao.tjut.bean.ClassBean;
import org.lanqiao.tjut.bean.SchClassBean;
import org.lanqiao.tjut.bean.ScoreBean;
import org.lanqiao.tjut.bean.StudentBean;
import org.lanqiao.tjut.bean.TeacherBean;
import org.lanqiao.tjut.db.DBDriverFactory;

public class SelectClassManagerDao {

	/**
	 * 保存教师选择的课程
	 * 
	 * @param clazz
	 *            课程对象
	 * @return 保存结果记录行数
	 */
	public int SavaClassT(ClassBean clazz) {
		int re_i = 0;
		String strSql = "INSERT INTO PDCLASS(CLASSNAME,TEANAME) VALUES(?,?)";
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql,
				new Object[] { clazz.getClassname(), clazz.getTeaname() });
		return re_i;
	}

	/**
	 * 学生退选课程
	 * 
	 * @param score
	 *            成绩表对象
	 * @return 修改记录行数
	 */
	public int deleteStuClass(ScoreBean score) {
		int re_i = 0;
		String strSql = "DELETE FROM PDSCORE WHERE STUNAME = ? AND CLASSNAME = ? AND TEANAME = ?";
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql,
				new Object[] { score.getStuname(), score.getClassname(), score.getTeaname() });
		return re_i;
	}

	/**
	 * 保存管理员添加的课程
	 * 
	 * @param SclassBean
	 *            课程对象
	 * @return 保存结果记录行数
	 */
	public int SaveClass(SchClassBean SclassBean) {
		int re_i = 0;
		if (SclassBean.getClassber() == null) {
			String strSql = "INSERT INTO PDSCCLASS(CLASSID,CLASSNAME) VALUES(?,?)";
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql,
					new Object[] { SclassBean.getClassid(), SclassBean.getClassname() });
		} else {
			String strSql = "UPDATE PDSCCLASS SET CLASSID = ?,CLASSNAME = ? WHERE CLASSBER = ?";
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql,
					new Object[] { SclassBean.getClassid(), SclassBean.getClassname(), SclassBean.getClassber() });
		}

		return re_i;
	}

	/**
	 * 查询学校课程
	 * 
	 * @return 查询结果集合
	 */
	public List<SchClassBean> getAllClass() {
		String strSql = "SELECT * FROM PDSCCLASS ORDER BY CLASSBER DESC";
		List<SchClassBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<SchClassBean>(SchClassBean.class));
		return lst;
	}

	/**
	 * 查询学校课程
	 * 
	 * @return 查询结果集合
	 */
	public List<SchClassBean> TeaSelectClass(TeacherBean teaBean) {
		String strSql = "SELECT CLASSNAME FROM PDSCCLASS MINUS SELECT CLASSNAME FROM PDCLASS WHERE TEANAME = ? ";
		List<SchClassBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<SchClassBean>(SchClassBean.class), new Object[] { teaBean.getTeaname() });
		return lst;
	}

	/**
	 * 查询学生未选的所有课程
	 * 
	 * @param stuBean
	 *            学生对象
	 * @return 查询结果集合
	 */
	public List<ClassBean> StuSelectClass(StudentBean stuBean) {
		String strSql = "SELECT CLASSNAME,TEANAME FROM PDCLASS MINUS SELECT CLASSNAME,TEANAME FROM PDSCORE WHERE STUNAME = ? ";
		List<ClassBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ClassBean>(ClassBean.class), new Object[] { stuBean.getStuname() });
		return lst;
	}

	/**
	 * 根据课程Num获取课程对象
	 * 
	 * @param strUid
	 *            课程Num
	 * @return 课程对象
	 */
	public SchClassBean getClassByNum(String strUid) {
		String strSql = "SELECT * FROM PDSCCLASS WHERE CLASSBER = ?";
		SchClassBean SclassB = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<SchClassBean>(SchClassBean.class), new Object[] { strUid });
		return SclassB;
	}

	/**
	 * 根据课程Num删除该课程信息
	 * 
	 * @param userId
	 *            课程Num
	 * @return 修改记录的行数
	 */
	public int deleteClass(int userId) {
		String strSql = "DELETE  FROM PDSCCLASS WHERE CLASSBER = ?";
		int re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { userId });
		return re_i;
	}

	/**
	 * 查询已开课程信息(模糊查询)
	 * 
	 * @param scClassB
	 *            课程对象
	 * @return 查询的课程信息
	 */
	public List<SchClassBean> getQueryClassInfo(SchClassBean scClassB) {
		// 根据条件进行数据查询，没有输入查询条件，则进行全部查询

		StringBuilder strSql = new StringBuilder("SELECT * FROM PDSCCLASS WHERE 1=1");

		// 根据查询条件动态拼写查询语句和参数
		List<Object> lstParams = new ArrayList<Object>();

		if (scClassB.getClassname() != null && !scClassB.getClassname().equals("")) {
			// 姓名查询

			strSql.append(" AND CLASSNAME LIKE ?");
			lstParams.add("%%" + scClassB.getClassname() + "%%");
		}

		// 给查询语句添加排序
		strSql.append(" ORDER BY CLASSBER");

		List<SchClassBean> lstclass = DBDriverFactory.getDBDriverInstance().query(strSql.toString(),
				new BeanListHandler<SchClassBean>(SchClassBean.class), lstParams.toArray());

		return lstclass;
	}

	/**
	 * 根据老师名从课程表中查出该老师选了哪些课程
	 * 
	 * @param clazz
	 *            课程表对象
	 * @return 该老师已选课程集合
	 */
	public List<ClassBean> QueryClassByTeaname(ClassBean clazz) {
		String strSql = "SELECT CLASSNAME FROM PDCLASS WHERE TEANAME= ?";
		List<ClassBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ClassBean>(ClassBean.class), new Object[] { clazz.getTeaname() });

		return lst;

	}

	/**
	 * 根据学生名从成绩表中查出该学生选了哪些课程
	 * 
	 * @param clazz
	 *            成绩表对象
	 * @return 该学生已选课程集合
	 */
	public List<ScoreBean> QueryClassByStuname(ScoreBean clazz) {
		String strSql = "SELECT CLASSNAME FROM PDSCORE WHERE STUNAME= ?";
		List<ScoreBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ScoreBean>(ScoreBean.class), new Object[] { clazz.getStuname() });

		return lst;

	}

	/**
	 * 在成绩保存学生选的的课程
	 * 
	 * @param clazz
	 *            课程对象
	 * @return 保存结果记录行数
	 */
	public int SavaClassS(ScoreBean score) {
		int re_i = 0;
		String strSql2 = "INSERT INTO PDSCORE(STUNAME,CLASSNAME,TEANAME) VALUES(?,?,?)";
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql2,
				new Object[] { score.getStuname(), score.getClassname(), score.getTeaname() });
		return re_i;

	}

	/**
	 * 获取教师所选的所有课程
	 * 
	 * @param clazz
	 *            课程对象
	 * @return 所有课程集合
	 */
	public List<ClassBean> getAllClassInfo(ClassBean clazz) {
		String strSql = "SELECT * FROM PDCLASS WHERE TEANAME = ? ORDER BY CLASSNUM DESC";
		List<ClassBean> lstclass = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ClassBean>(ClassBean.class), new Object[] { clazz.getTeaname() });
		return lstclass;
	}

	/**
	 * 获取学生所选的所有课程
	 * 
	 * @param clazz
	 *            课程对象
	 * @return 所有课程集合
	 */
	public List<ScoreBean> getStuAllClassInfo(ScoreBean score) {
		String strSql = "SELECT * FROM PDSCORE WHERE STUNAME = ? ORDER BY SCOREID DESC";
		List<ScoreBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ScoreBean>(ScoreBean.class), new Object[] { score.getStuname() });
		return lst;

	}

	/**
	 * 通过课程名获取成绩表中学生对象
	 * 
	 * @param strUid
	 *            课程名
	 * @return 对象
	 */
	public List<ScoreBean> getStuInfoByClassName(String strUid, String teaname) {
		String strSql = "SELECT * FROM PDSCORE WHERE CLASSNAME = ? AND TEANAME = ?";
		List<ScoreBean> lst = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ScoreBean>(ScoreBean.class), new Object[] { strUid, teaname });
		return lst;
	}

	/**
	 * 通过课程名获取成绩表中学生对象
	 * 
	 * @param strUid
	 *            课程名
	 * @return 对象
	 */
	public ScoreBean getStuInfoByStuName(String strUid, String classname) {
		String strSql = "SELECT * FROM PDSCORE WHERE STUNAME =? AND CLASSNAME = ?";
		ScoreBean sbean = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<ScoreBean>(ScoreBean.class), new Object[] { strUid, classname });
		return sbean;
	}

	/**
	 * 修改成绩表中学生成绩
	 * 
	 * @param txtscores
	 *            参数字符串
	 * @return 修改记录的行数
	 */
	public int[] updateStuScore(String txtscores) {

		String strSql = "UPDATE PDSCORE SET SCORE = ? WHERE SCOREID = ?";
		int[] i = DBDriverFactory.getDBDriverInstance().batch(strSql, strParasToObject(txtscores));
		return i;
	}

	/**
	 * 将字符串转换为批处理需要的二维数组参数的格式
	 * 
	 * @param txtscores
	 *            参数字符串
	 * @return 二维数组参数
	 */
	private Object[][] strParasToObject(String txtscores) {
		Object[][] arrParas = null;
		if (txtscores != null && !txtscores.trim().equals("")) {
			// 解析字符串
			String[] strArr = txtscores.split(",");
			arrParas = new Object[strArr.length][2];
			for (int i = 0; i < strArr.length; i++) {
				String[] strSubArr = strArr[i].split(":");
				if (strSubArr.length == 2) {
					arrParas[i] = new Object[] { strSubArr[1], strSubArr[0] };
				}
			}
		}
		return arrParas;
	}

}
